using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace TrainTicket
{
    public class DbConnection
    {
        public DbConnection() { }

        private String ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["conn"].ConnectionString; }
        }

        // get data return dataset user param, no param
        public DataSet ExecuteDataSet(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataSet Ds = new DataSet();
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
                cn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(Ds);
                return Ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }
        }


        // get data return datatable user param, no param
        public DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
                cn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }
        }

        // insert, delete,update return datatable user param, no param
        public int ExecuteNonQuery(string storedProcedureName, params SqlParameter[] arrParam)
        {
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
                cn.Open();
            try
            {

                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                int rowsAffected = cmd.ExecuteNonQuery();
                return rowsAffected;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }
        }

        public int GetInsertedIdWithExecuteNonQuery(String storedProcedureName, params SqlParameter[] arrParam)
        {
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
            cmd.CommandType = CommandType.StoredProcedure;

            if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
                cn.Open();

            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                        cmd.Parameters.Add(param);
                }

                cmd.ExecuteNonQuery();

                int insertedId = (int)cmd.Parameters[cmd.Parameters.Count - 1].Value;
                return insertedId;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }
        }


        // tra ve gia tri
        public int ExecuteScalar(string storedProcedureName, params SqlParameter[] arrParam)
        {
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
                cn.Open();
            try
            {

                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                int rowsAffected = Convert.ToInt32(cmd.ExecuteScalar());
                return rowsAffected;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }
        }

    }
}
